GoogleCloud WorkflowsのGoogle Sheets APIコネクタでGoogleスプレッドシートのデータをBigQueryに取り込んでみる

$ bq show --schema --format=prettyjson data_set_weather.jp_weather
[
  {
    "mode": "NULLABLE",
    "name": "date",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "month",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "city_name",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "weather_condition",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "temperature",
    "type": "NUMERIC"
  },
  {
    "mode": "NULLABLE",
    "name": "humidity",
    "type": "NUMERIC"
  },
  {
    "mode": "NULLABLE",
    "name": "sunshine_duration",
    "type": "NUMERIC"
  },
  {
    "mode": "NULLABLE",
    "name": "cloud_cover",
    "type": "NUMERIC"
  }
]

main:
  steps:
    - init:
        assign:
          - project_id: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
          - destination_dataset: "data_set_weather" # ターゲットとなるBigQueryのデータセット
          - destination_table: "jp_weather" # ターゲットとなるBigQueryのテーブル
          - location: "asia-northeast1"
          - sheetId: "{スプレッドシートID}" #
          - getRange: "Sheet1!A2:H50"  # 読み込む範囲のA1表記またはR1C1表記
          - insert_data: ""
    - get_from_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.get
        args:
          range: ${getRange}
          spreadsheetId: ${sheetId}
          dateTimeRenderOption:
        result: getResult

    - process_result:
        for:
          value: row
          in: ${getResult.values}
          steps:
            - process_each_row:
                assign:
                  - date: ${row[0]}
                  - month: ${row[1]}
                  - city_name: ${row[2]}
                  - weather_condition: ${row[3]}
                  - temperature: ${row[4]}
                  - humidity: ${row[5]}
                  - sunshine_duration: ${row[6]}
                  - cloud_cover: ${row[7]}
                  - row:  ${"('"+date+"', "+month+", '"+city_name+"', '"+weather_condition+"', "+temperature+", "+humidity+", "+sunshine_duration+", "+cloud_cover +")"}
                  - sep: ${if(len(insert_data) > 0, ",", "")}
                  - insert_data: ${insert_data + sep + row}
    - construct_query:
        assign:
          - query: ${"INSERT INTO `" + project_id + "." + destination_dataset + "." + destination_table + "` (date, month, city_name, weather_condition, temperature, humidity, sunshine_duration, cloud_cover) VALUES " + insert_data}

    - insert_data:
        call: googleapis.bigquery.v2.jobs.query
        args:
          projectId: ${project_id}
          body:
            query: ${query}
            useLegacySql: false
        result: queryResult
    - the_end:
        return: ${queryResult}

gcloud workflows deploy jp-weather-sheets2bq --source=jp-weather-sheets2bq.yml --service-account jp-weather-2@{プロジェクト名}.iam.gserviceaccount.com --location asia-northeast1

$ gcloud services enable drive.googleapis.com sheets.googleapis.com

$ gcloud workflows run jp-weather-sheets2bq --location asia-northeast1
Waiting for execution [89b765cc-eea4-45df-b107-09fd1c222abe] to complete...done.
...
startTime: '2024-08-30T11:33:40.494290Z'
state: SUCCEEDED
status:
  currentSteps:
  - routine: main
    step: the_end
workflowRevisionId: 000041-429

$ bq query "select * from data_set_weather.jp_weather limit 10" 
+------------+-------+-----------+-------------------+-------------+----------+-------------------+-------------+
|    date    | month | city_name | weather_condition | temperature | humidity | sunshine_duration | cloud_cover |
+------------+-------+-----------+-------------------+-------------+----------+-------------------+-------------+
2024-08-10   |     1 | 東京       | 晴                |        27.8 |       79 |               0.0 |           1 |
2024-08-10   |     2 | 東京       | 晴                |        27.8 |       80 |               0.0 |           1 |
2024-08-10   |     3 | 東京       | 晴                |        27.5 |       80 |               0.0 |           1 |
2024-08-10   |     4 | 東京       | 晴                |        27.5 |       82 |               0.0 |           1 |
2024-08-10   |     5 | 東京       | 晴                |        27.5 |       80 |               0.0 |           0 |
2024-08-10   |     6 | 東京       | 晴                |        27.4 |       76 |               0.0 |           0 |
2024-08-10   |     7 | 東京       | 晴                |        28.3 |       72 |               0.4 |           0 |
2024-08-10   |     8 | 東京       | 晴                |        29.4 |       67 |               0.9 |           0 |
2024-08-10   |     9 | 東京       | 晴                |        31.2 |       62 |               1.0 |           0 |
2024-08-10   |    10 | 東京       | 晴                |        31.0 |       59 |               0.5 |           0 |
+------------+-------+-----------+-------------------+-------------+----------+-------------------+-------------+